﻿using System;
using System.Collections;
using NHibernate;
using NHibernate.Cfg;
using System.Collections.Generic;

namespace Business
{
    public class CodeType
    {
        private ISessionFactory _sessionFactory;
        public CodeType()
        {
            _sessionFactory = DataObjects.Service.SessionFactory.GetInstance().GetNHibernateFactory();
        }
        
        public ArrayList List()
        {
            ArrayList arrRet = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                arrRet = (ArrayList)session.CreateCriteria(typeof(DataObjects.CodeType)).List();
                tx.Commit();
            }
            return arrRet;
        }

        public ArrayList GetListCodeTypeByGroup(string sgroupid)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                string sql = "select * from Code_Type where GROUP_ID = ? ";
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.CodeType));
                query.SetString(0, sgroupid);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                return arrTemp;
            }
        }

        public ArrayList GetListCodeTypeByGroup(string sgroupid, string exclude)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                string sql = "select * from Code_Type where GROUP_ID = ? and Code_Type_Id <> ?";
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.CodeType));
                query.SetString(0, sgroupid);
                query.SetString(1, exclude);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                return arrTemp;
            }
        }

        public ArrayList GetListCodeTypeByGroupNFreeField1(string sgroupid, string sfreefield1)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                string sql = "select * from Code_Type where GROUP_ID = ? and FREE_FIELD1 = ? ";
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.CodeType));
                query.SetString(0, sgroupid);
                query.SetString(1, sfreefield1);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                return arrTemp;
            }
        }

        public DataObjects.CodeType GetObjectByPK(string codetypeId)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("select top 1 * from Code_Type where Code_Type_ID =?").AddEntity(typeof(DataObjects.CodeType));
                query.SetString(0, codetypeId);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                if (arrTemp.Count > 0)
                {
                    return (DataObjects.CodeType)arrTemp[0];
                }
                return null;
            }
        }

        public int GetMaxID(string sgroupid)
        {
            ArrayList arrTemp = new ArrayList();
            string sql = "select top 1 * from Code_Type where GROUP_ID = ? order by cast( substring(Code_Type_Id,_NUM_,15) as float ) desc";
            switch (sgroupid)
            {
                case "GENDER":
                    sql = sql.Replace("_NUM_", "8");
                    break;
                case "NATION":
                    sql = sql.Replace("_NUM_", "8");
                    break;
                case "EDU":
                    sql = sql.Replace("_NUM_", "5");
                    break;
                case "BANK":
                    sql = sql.Replace("_NUM_", "6");
                    break;
                case "KOC":
                    sql = sql.Replace("_NUM_", "5");
                    break;
                case "SERV":
                    sql = sql.Replace("_NUM_", "6");
                    break;
                case "TRANS":
                    sql = sql.Replace("_NUM_", "7");
                    break;
            }

            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.CodeType));
                query.SetString(0, sgroupid);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                DataObjects.CodeType oCodeType;

                if (arrTemp.Count > 0)
                {
                    oCodeType = (DataObjects.CodeType)arrTemp[0];
                    if (oCodeType != null)
                    {
                        string[] arr;
                        arr = oCodeType.CodeTypeId.Split(new char[] { '_' });
                        return int.Parse(arr[1]);
                    }
                }
                return 0;
            }
        }

        //code insert - update - delete 
        public void Insert(DataObjects.CodeType oCodeType)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                session.Save(oCodeType);
                tx.Commit();			        
            }
        }
        public void Update(DataObjects.CodeType oCodeType)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                session.Update(oCodeType);
                tx.Commit();		        
            }
        }
        public void DeleteByPK(string codetypeId)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                DataObjects.CodeType oCodeType = (DataObjects.CodeType)session.Load(typeof(DataObjects.CodeType), codetypeId);
                session.Delete(oCodeType);
                tx.Commit();
            }
        }
    }
}
